{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(mysql+pymysql://bigdata1902:***@120.92.43.237:3306/word?charset=utf8)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine= create_engine('mysql+pymysql://bigdata1902:bigdata1902@ABC@120.92.43.237:3306/word?charset=utf8')\n",
    "engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine1 = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/dsj?charset=utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(mysql+pymysql://root:***@127.0.0.1:3306/dsj?charset=utf8)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'C:\\\\Users\\\\Administrator\\\\Desktop\\\\超市营业额2.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-6-ed302ed494c1>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mr'C:\\Users\\Administrator\\Desktop\\超市营业额2.xlsx'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdf_obj\u001b[0m \u001b[1;33m=\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mby\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'时段'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'交易额'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msum\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf_obj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mdf_obj2\u001b[0m \u001b[1;33m=\u001b[0m\u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mby\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'时段'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'交易额'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmean\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf_obj2\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\util\\_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    206\u001b[0m                 \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    207\u001b[0m                     \u001b[0mkwargs\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mnew_arg_name\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnew_arg_value\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 208\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    209\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    210\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, verbose, parse_dates, date_parser, thousands, comment, skip_footer, skipfooter, convert_float, mangle_dupe_cols, **kwds)\u001b[0m\n\u001b[0;32m    308\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    309\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 310\u001b[1;33m         \u001b[0mio\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    311\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m!=\u001b[0m \u001b[0mio\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    312\u001b[0m         raise ValueError(\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, io, engine)\u001b[0m\n\u001b[0;32m    817\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_io\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_stringify_path\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    818\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 819\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engines\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_io\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    820\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    821\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__fspath__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\excel\\_xlrd.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, filepath_or_buffer)\u001b[0m\n\u001b[0;32m     19\u001b[0m         \u001b[0merr_msg\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"Install xlrd >= 1.0.0 for Excel support\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     20\u001b[0m         \u001b[0mimport_optional_dependency\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"xlrd\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mextra\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merr_msg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 21\u001b[1;33m         \u001b[0msuper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__init__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     22\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     23\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, filepath_or_buffer)\u001b[0m\n\u001b[0;32m    357\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbook\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    358\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 359\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbook\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    360\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    361\u001b[0m             raise ValueError(\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\io\\excel\\_xlrd.py\u001b[0m in \u001b[0;36mload_workbook\u001b[1;34m(self, filepath_or_buffer)\u001b[0m\n\u001b[0;32m     34\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mopen_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfile_contents\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     35\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 36\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mopen_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     37\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     38\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\xlrd\\__init__.py\u001b[0m in \u001b[0;36mopen_workbook\u001b[1;34m(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows)\u001b[0m\n\u001b[0;32m    109\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    110\u001b[0m         \u001b[0mfilename\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexpanduser\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilename\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 111\u001b[1;33m         \u001b[1;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilename\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"rb\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    112\u001b[0m             \u001b[0mpeek\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpeeksz\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    113\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mpeek\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34mb\"PK\\x03\\x04\"\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;31m# a ZIP file\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'C:\\\\Users\\\\Administrator\\\\Desktop\\\\超市营业额2.xlsx'"
     ]
    }
   ],
   "source": [
    "df = pd.read_excel(r'C:\\Users\\Administrator\\Desktop\\超市营业额2.xlsx')\n",
    "df_obj =df.groupby(by='时段')['交易额'].sum()\n",
    "print(df_obj)\n",
    "df_obj2 =df.groupby(by='时段')['交易额'].mean()\n",
    "print(df_obj2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-7-b7a6117fad36>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mby\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'姓名'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'时段'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'交易额'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msum\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "df.groupby(by=['姓名','时段'])['交易额'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'A1' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-4a5b95edbea7>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mA1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'A1' is not defined"
     ]
    }
   ],
   "source": [
    "A1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "A2 = lambda s:s.max()-s.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(by='姓名')['交易额'].agg(A2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "detail = pd.read_sql('meal_order_detail1',con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>detail_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>dishes_id</th>\n",
       "      <th>logicprn_name</th>\n",
       "      <th>parent_class_name</th>\n",
       "      <th>dishes_name</th>\n",
       "      <th>itemis_add</th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "      <th>cost</th>\n",
       "      <th>place_order_time</th>\n",
       "      <th>discount_amt</th>\n",
       "      <th>discount_reason</th>\n",
       "      <th>kick_back</th>\n",
       "      <th>add_inprice</th>\n",
       "      <th>add_info</th>\n",
       "      <th>bar_code</th>\n",
       "      <th>picture_file</th>\n",
       "      <th>emp_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2956</td>\n",
       "      <td>417</td>\n",
       "      <td>610062</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜蓉生蚝</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:05:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/104001.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2958</td>\n",
       "      <td>417</td>\n",
       "      <td>609957</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒙古烤羊腿</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/202003.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2961</td>\n",
       "      <td>417</td>\n",
       "      <td>609950</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>大蒜苋菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/303001.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2966</td>\n",
       "      <td>417</td>\n",
       "      <td>610038</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>芝麻烤紫菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/105002.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2968</td>\n",
       "      <td>417</td>\n",
       "      <td>610003</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜香包</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/503002.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2774</td>\n",
       "      <td>6750</td>\n",
       "      <td>774</td>\n",
       "      <td>610011</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>白饭/大碗</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/601005.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2775</td>\n",
       "      <td>6742</td>\n",
       "      <td>774</td>\n",
       "      <td>609996</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>牛尾汤</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/201006.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2776</td>\n",
       "      <td>6756</td>\n",
       "      <td>774</td>\n",
       "      <td>609949</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>意文柠檬汁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:01:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/404005.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2777</td>\n",
       "      <td>6763</td>\n",
       "      <td>774</td>\n",
       "      <td>610014</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>金玉良缘</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:03:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302003.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2778</td>\n",
       "      <td>6764</td>\n",
       "      <td>774</td>\n",
       "      <td>610017</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>酸辣藕丁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:04:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302006.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2779 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     detail_id order_id dishes_id logicprn_name parent_class_name dishes_name  \\\n",
       "0         2956      417    610062            NA                NA        蒜蓉生蚝   \n",
       "1         2958      417    609957            NA                NA       蒙古烤羊腿   \n",
       "2         2961      417    609950            NA                NA        大蒜苋菜   \n",
       "3         2966      417    610038            NA                NA       芝麻烤紫菜   \n",
       "4         2968      417    610003            NA                NA         蒜香包   \n",
       "...        ...      ...       ...           ...               ...         ...   \n",
       "2774      6750      774    610011            NA                NA       白饭/大碗   \n",
       "2775      6742      774    609996            NA                NA         牛尾汤   \n",
       "2776      6756      774    609949            NA                NA      意文柠檬汁    \n",
       "2777      6763      774    610014            NA                NA        金玉良缘   \n",
       "2778      6764      774    610017            NA                NA        酸辣藕丁   \n",
       "\n",
       "     itemis_add  counts  amounts cost    place_order_time discount_amt  \\\n",
       "0             0     1.0     49.0   NA 2016-08-01 11:05:00           NA   \n",
       "1             0     1.0     48.0   NA 2016-08-01 11:07:00           NA   \n",
       "2             0     1.0     30.0   NA 2016-08-01 11:07:00           NA   \n",
       "3             0     1.0     25.0   NA 2016-08-01 11:11:00           NA   \n",
       "4             0     1.0     13.0   NA 2016-08-01 11:11:00           NA   \n",
       "...         ...     ...      ...  ...                 ...          ...   \n",
       "2774          0     1.0     10.0   NA 2016-08-10 21:56:00           NA   \n",
       "2775          0     1.0     40.0   NA 2016-08-10 21:56:00           NA   \n",
       "2776          0     1.0     13.0   NA 2016-08-10 22:01:00           NA   \n",
       "2777          0     1.0     30.0   NA 2016-08-10 22:03:00           NA   \n",
       "2778          0     1.0     33.0   NA 2016-08-10 22:04:00           NA   \n",
       "\n",
       "     discount_reason kick_back add_inprice add_info bar_code  \\\n",
       "0                 NA        NA           0       NA       NA   \n",
       "1                 NA        NA           0       NA       NA   \n",
       "2                 NA        NA           0       NA       NA   \n",
       "3                 NA        NA           0       NA       NA   \n",
       "4                 NA        NA           0       NA       NA   \n",
       "...              ...       ...         ...      ...      ...   \n",
       "2774              NA        NA           0       NA       NA   \n",
       "2775              NA        NA           0       NA       NA   \n",
       "2776              NA        NA           0       NA       NA   \n",
       "2777              NA        NA           0       NA       NA   \n",
       "2778              NA        NA           0       NA       NA   \n",
       "\n",
       "          picture_file emp_id  \n",
       "0     caipu/104001.jpg   1442  \n",
       "1     caipu/202003.jpg   1442  \n",
       "2     caipu/303001.jpg   1442  \n",
       "3     caipu/105002.jpg   1442  \n",
       "4     caipu/503002.jpg   1442  \n",
       "...                ...    ...  \n",
       "2774  caipu/601005.jpg   1138  \n",
       "2775  caipu/201006.jpg   1138  \n",
       "2776  caipu/404005.jpg   1138  \n",
       "2777  caipu/302003.jpg   1138  \n",
       "2778  caipu/302006.jpg   1138  \n",
       "\n",
       "[2779 rows x 19 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "detail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "detail.groupby(by='order_id')['amounts'].transform(lambda x:x/x.sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.pivot_table(df,values='交易额',index='日期',columns='姓名',aggfunc='sum',margins='true',fill_value=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "A=[]\n",
    "for i in detail['place_order_time']:\n",
    "    A.append(pd.Timestamp(i).day)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "detail['day_a']=A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>detail_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>dishes_id</th>\n",
       "      <th>logicprn_name</th>\n",
       "      <th>parent_class_name</th>\n",
       "      <th>dishes_name</th>\n",
       "      <th>itemis_add</th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "      <th>cost</th>\n",
       "      <th>place_order_time</th>\n",
       "      <th>discount_amt</th>\n",
       "      <th>discount_reason</th>\n",
       "      <th>kick_back</th>\n",
       "      <th>add_inprice</th>\n",
       "      <th>add_info</th>\n",
       "      <th>bar_code</th>\n",
       "      <th>picture_file</th>\n",
       "      <th>emp_id</th>\n",
       "      <th>day_a</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2956</td>\n",
       "      <td>417</td>\n",
       "      <td>610062</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜蓉生蚝</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:05:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/104001.jpg</td>\n",
       "      <td>1442</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2958</td>\n",
       "      <td>417</td>\n",
       "      <td>609957</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒙古烤羊腿</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/202003.jpg</td>\n",
       "      <td>1442</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2961</td>\n",
       "      <td>417</td>\n",
       "      <td>609950</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>大蒜苋菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/303001.jpg</td>\n",
       "      <td>1442</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2966</td>\n",
       "      <td>417</td>\n",
       "      <td>610038</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>芝麻烤紫菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/105002.jpg</td>\n",
       "      <td>1442</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2968</td>\n",
       "      <td>417</td>\n",
       "      <td>610003</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜香包</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/503002.jpg</td>\n",
       "      <td>1442</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2774</td>\n",
       "      <td>6750</td>\n",
       "      <td>774</td>\n",
       "      <td>610011</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>白饭/大碗</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/601005.jpg</td>\n",
       "      <td>1138</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2775</td>\n",
       "      <td>6742</td>\n",
       "      <td>774</td>\n",
       "      <td>609996</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>牛尾汤</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/201006.jpg</td>\n",
       "      <td>1138</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2776</td>\n",
       "      <td>6756</td>\n",
       "      <td>774</td>\n",
       "      <td>609949</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>意文柠檬汁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:01:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/404005.jpg</td>\n",
       "      <td>1138</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2777</td>\n",
       "      <td>6763</td>\n",
       "      <td>774</td>\n",
       "      <td>610014</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>金玉良缘</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:03:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302003.jpg</td>\n",
       "      <td>1138</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2778</td>\n",
       "      <td>6764</td>\n",
       "      <td>774</td>\n",
       "      <td>610017</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>酸辣藕丁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:04:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302006.jpg</td>\n",
       "      <td>1138</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2779 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     detail_id order_id dishes_id logicprn_name parent_class_name dishes_name  \\\n",
       "0         2956      417    610062            NA                NA        蒜蓉生蚝   \n",
       "1         2958      417    609957            NA                NA       蒙古烤羊腿   \n",
       "2         2961      417    609950            NA                NA        大蒜苋菜   \n",
       "3         2966      417    610038            NA                NA       芝麻烤紫菜   \n",
       "4         2968      417    610003            NA                NA         蒜香包   \n",
       "...        ...      ...       ...           ...               ...         ...   \n",
       "2774      6750      774    610011            NA                NA       白饭/大碗   \n",
       "2775      6742      774    609996            NA                NA         牛尾汤   \n",
       "2776      6756      774    609949            NA                NA      意文柠檬汁    \n",
       "2777      6763      774    610014            NA                NA        金玉良缘   \n",
       "2778      6764      774    610017            NA                NA        酸辣藕丁   \n",
       "\n",
       "     itemis_add  counts  amounts cost    place_order_time discount_amt  \\\n",
       "0             0     1.0     49.0   NA 2016-08-01 11:05:00           NA   \n",
       "1             0     1.0     48.0   NA 2016-08-01 11:07:00           NA   \n",
       "2             0     1.0     30.0   NA 2016-08-01 11:07:00           NA   \n",
       "3             0     1.0     25.0   NA 2016-08-01 11:11:00           NA   \n",
       "4             0     1.0     13.0   NA 2016-08-01 11:11:00           NA   \n",
       "...         ...     ...      ...  ...                 ...          ...   \n",
       "2774          0     1.0     10.0   NA 2016-08-10 21:56:00           NA   \n",
       "2775          0     1.0     40.0   NA 2016-08-10 21:56:00           NA   \n",
       "2776          0     1.0     13.0   NA 2016-08-10 22:01:00           NA   \n",
       "2777          0     1.0     30.0   NA 2016-08-10 22:03:00           NA   \n",
       "2778          0     1.0     33.0   NA 2016-08-10 22:04:00           NA   \n",
       "\n",
       "     discount_reason kick_back add_inprice add_info bar_code  \\\n",
       "0                 NA        NA           0       NA       NA   \n",
       "1                 NA        NA           0       NA       NA   \n",
       "2                 NA        NA           0       NA       NA   \n",
       "3                 NA        NA           0       NA       NA   \n",
       "4                 NA        NA           0       NA       NA   \n",
       "...              ...       ...         ...      ...      ...   \n",
       "2774              NA        NA           0       NA       NA   \n",
       "2775              NA        NA           0       NA       NA   \n",
       "2776              NA        NA           0       NA       NA   \n",
       "2777              NA        NA           0       NA       NA   \n",
       "2778              NA        NA           0       NA       NA   \n",
       "\n",
       "          picture_file emp_id  day_a  \n",
       "0     caipu/104001.jpg   1442      1  \n",
       "1     caipu/202003.jpg   1442      1  \n",
       "2     caipu/303001.jpg   1442      1  \n",
       "3     caipu/105002.jpg   1442      1  \n",
       "4     caipu/503002.jpg   1442      1  \n",
       "...                ...    ...    ...  \n",
       "2774  caipu/601005.jpg   1138     10  \n",
       "2775  caipu/201006.jpg   1138     10  \n",
       "2776  caipu/404005.jpg   1138     10  \n",
       "2777  caipu/302003.jpg   1138     10  \n",
       "2778  caipu/302006.jpg   1138     10  \n",
       "\n",
       "[2779 rows x 20 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "detail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "day_a\n",
       "1      9366.0\n",
       "2      6125.0\n",
       "3      6890.0\n",
       "4      7549.0\n",
       "5      8671.0\n",
       "6     32167.0\n",
       "7     31306.0\n",
       "8      6532.0\n",
       "9      7155.0\n",
       "10    10231.0\n",
       "Name: amounts, dtype: float64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cy_a = detail.groupby(by='day_a')['amounts'].sum()\n",
    "cy_a"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>工号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>日期</th>\n",
       "      <th>时段</th>\n",
       "      <th>交易额</th>\n",
       "      <th>柜台</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1001</td>\n",
       "      <td>张三</td>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>9：00-14：00</td>\n",
       "      <td>1664.0</td>\n",
       "      <td>化妆品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1002</td>\n",
       "      <td>李四</td>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>14：00-21：00</td>\n",
       "      <td>954.0</td>\n",
       "      <td>化妆品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1003</td>\n",
       "      <td>王五</td>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>9：00-14：00</td>\n",
       "      <td>1407.0</td>\n",
       "      <td>食品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1004</td>\n",
       "      <td>赵六</td>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>14：00-21：00</td>\n",
       "      <td>1320.0</td>\n",
       "      <td>食品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1005</td>\n",
       "      <td>周七</td>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>9：00-14：00</td>\n",
       "      <td>994.0</td>\n",
       "      <td>日用品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>244</td>\n",
       "      <td>1002</td>\n",
       "      <td>李四</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>14：00-21：00</td>\n",
       "      <td>859.0</td>\n",
       "      <td>蔬菜水果</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>245</td>\n",
       "      <td>1004</td>\n",
       "      <td>赵六</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>9：00-14：00</td>\n",
       "      <td>1668.0</td>\n",
       "      <td>日用品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>246</td>\n",
       "      <td>1004</td>\n",
       "      <td>赵六</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>14：00-21：00</td>\n",
       "      <td>1722.0</td>\n",
       "      <td>日用品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>247</td>\n",
       "      <td>1003</td>\n",
       "      <td>王五</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>9：00-14：00</td>\n",
       "      <td>1274.0</td>\n",
       "      <td>食品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>248</td>\n",
       "      <td>1006</td>\n",
       "      <td>钱八</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>14：00-21：00</td>\n",
       "      <td>812.0</td>\n",
       "      <td>食品</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>249 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       工号  姓名          日期           时段     交易额    柜台\n",
       "0    1001  张三  2019-03-01   9：00-14：00  1664.0   化妆品\n",
       "1    1002  李四  2019-03-01  14：00-21：00   954.0   化妆品\n",
       "2    1003  王五  2019-03-01   9：00-14：00  1407.0    食品\n",
       "3    1004  赵六  2019-03-01  14：00-21：00  1320.0    食品\n",
       "4    1005  周七  2019-03-01   9：00-14：00   994.0   日用品\n",
       "..    ...  ..         ...          ...     ...   ...\n",
       "244  1002  李四  2019-03-31  14：00-21：00   859.0  蔬菜水果\n",
       "245  1004  赵六  2019-03-31   9：00-14：00  1668.0   日用品\n",
       "246  1004  赵六  2019-03-31  14：00-21：00  1722.0   日用品\n",
       "247  1003  王五  2019-03-31   9：00-14：00  1274.0    食品\n",
       "248  1006  钱八  2019-03-31  14：00-21：00   812.0    食品\n",
       "\n",
       "[249 rows x 6 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cs = pd.read_excel(r'D:/Desktop/超市营业额2.xlsx')\n",
    "cs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "日期\n",
       "2019-03-01    10428.0\n",
       "2019-03-02    10180.0\n",
       "2019-03-03    11104.0\n",
       "2019-03-04    11160.0\n",
       "2019-03-05    10883.0\n",
       "2019-03-06    10142.0\n",
       "2019-03-07     9703.0\n",
       "2019-03-08     9242.0\n",
       "2019-03-09    10098.0\n",
       "2019-03-10     8789.0\n",
       "2019-03-11    10110.0\n",
       "2019-03-12    10228.0\n",
       "2019-03-13    10085.0\n",
       "2019-03-14    19271.0\n",
       "2019-03-15     9970.0\n",
       "2019-03-16     9425.0\n",
       "2019-03-17     9305.0\n",
       "2019-03-18     9688.0\n",
       "2019-03-19     8974.0\n",
       "2019-03-20     9973.0\n",
       "2019-03-21     8661.0\n",
       "2019-03-22    10151.0\n",
       "2019-03-23    11733.0\n",
       "2019-03-24    10999.0\n",
       "2019-03-25     8498.0\n",
       "2019-03-26    11441.0\n",
       "2019-03-27     8956.0\n",
       "2019-03-28    17268.0\n",
       "2019-03-29    11302.0\n",
       "2019-03-30     9516.0\n",
       "2019-03-31     9974.0\n",
       "Name: 交易额, dtype: float64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rq = cs.groupby(by = '日期')['交易额'].sum()\n",
    "rq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "日期\n",
       "2019-03-25    8498.0\n",
       "2019-03-21    8661.0\n",
       "2019-03-10    8789.0\n",
       "Name: 交易额, dtype: float64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xq = rq.sort_values()[:3]\n",
    "xq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Monday\n",
      "Thursday\n",
      "Sunday\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: FutureWarning: `weekday_name` is deprecated and will be removed in a future version. Use `day_name` instead\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "for i in xq.index:\n",
    "#     print(i)\n",
    "    print(pd.Timestamp(i).weekday_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>出生年份（年）</th>\n",
       "      <th>年龄（岁）</th>\n",
       "      <th>身高(cm)</th>\n",
       "      <th>体重(kg)</th>\n",
       "      <th>项目</th>\n",
       "      <th>省份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>陈楠</td>\n",
       "      <td>女</td>\n",
       "      <td>1983年</td>\n",
       "      <td>35</td>\n",
       "      <td>197</td>\n",
       "      <td>90</td>\n",
       "      <td>篮球</td>\n",
       "      <td>山东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>白发全</td>\n",
       "      <td>男</td>\n",
       "      <td>1986年</td>\n",
       "      <td>32</td>\n",
       "      <td>175</td>\n",
       "      <td>64</td>\n",
       "      <td>铁人三项</td>\n",
       "      <td>云南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>陈晓佳</td>\n",
       "      <td>女</td>\n",
       "      <td>1988年</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>70</td>\n",
       "      <td>篮球</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>陈倩</td>\n",
       "      <td>女</td>\n",
       "      <td>1987年</td>\n",
       "      <td>31</td>\n",
       "      <td>163</td>\n",
       "      <td>54</td>\n",
       "      <td>女子现代五项</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>曹忠荣</td>\n",
       "      <td>男</td>\n",
       "      <td>1981年</td>\n",
       "      <td>37</td>\n",
       "      <td>180</td>\n",
       "      <td>73</td>\n",
       "      <td>男子现代五项</td>\n",
       "      <td>上海市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>174</td>\n",
       "      <td>赵芸蕾</td>\n",
       "      <td>女</td>\n",
       "      <td>1986年</td>\n",
       "      <td>32</td>\n",
       "      <td>173</td>\n",
       "      <td>62</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>175</td>\n",
       "      <td>周琦</td>\n",
       "      <td>男</td>\n",
       "      <td>1996年</td>\n",
       "      <td>22</td>\n",
       "      <td>217</td>\n",
       "      <td>95</td>\n",
       "      <td>篮球</td>\n",
       "      <td>河南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>176</td>\n",
       "      <td>翟晓川</td>\n",
       "      <td>男</td>\n",
       "      <td>1993年</td>\n",
       "      <td>25</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>篮球</td>\n",
       "      <td>河北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>177</td>\n",
       "      <td>赵继伟</td>\n",
       "      <td>男</td>\n",
       "      <td>1995年</td>\n",
       "      <td>23</td>\n",
       "      <td>185</td>\n",
       "      <td>77</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>178</td>\n",
       "      <td>邹雨宸</td>\n",
       "      <td>男</td>\n",
       "      <td>1996年</td>\n",
       "      <td>22</td>\n",
       "      <td>208</td>\n",
       "      <td>108</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>179 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       姓名 性别 出生年份（年）  年龄（岁）  身高(cm)  体重(kg)      项目     省份\n",
       "0     陈楠   女   1983年     35     197      90      篮球    山东省\n",
       "1    白发全   男   1986年     32     175      64    铁人三项    云南省\n",
       "2    陈晓佳   女   1988年     30     180      70      篮球    江苏省\n",
       "3     陈倩   女   1987年     31     163      54  女子现代五项  江苏省  \n",
       "4    曹忠荣   男   1981年     37     180      73  男子现代五项    上海市\n",
       "..    ... ..     ...    ...     ...     ...     ...    ...\n",
       "174  赵芸蕾   女   1986年     32     173      62     羽毛球    湖北省\n",
       "175   周琦   男   1996年     22     217      95      篮球   河南省 \n",
       "176  翟晓川   男   1993年     25     204     100      篮球   河北省 \n",
       "177  赵继伟   男   1995年     23     185      77      篮球    辽宁省\n",
       "178  邹雨宸   男   1996年     22     208     108      篮球    辽宁省\n",
       "\n",
       "[179 rows x 8 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ydy = pd.read_csv(r'D:/Desktop/运动员信息表.csv',encoding='gbk')\n",
    "ydy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>出生年份（年）</th>\n",
       "      <th>年龄（岁）</th>\n",
       "      <th>身高(cm)</th>\n",
       "      <th>体重(kg)</th>\n",
       "      <th>项目</th>\n",
       "      <th>省份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>丁彦雨航</td>\n",
       "      <td>男</td>\n",
       "      <td>1993年</td>\n",
       "      <td>25</td>\n",
       "      <td>200</td>\n",
       "      <td>91</td>\n",
       "      <td>篮球</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>郭艾伦</td>\n",
       "      <td>男</td>\n",
       "      <td>1993年</td>\n",
       "      <td>25</td>\n",
       "      <td>192</td>\n",
       "      <td>85</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>48</td>\n",
       "      <td>李慕豪</td>\n",
       "      <td>男</td>\n",
       "      <td>1992年</td>\n",
       "      <td>26</td>\n",
       "      <td>225</td>\n",
       "      <td>111</td>\n",
       "      <td>篮球</td>\n",
       "      <td>贵州贵阳</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>106</td>\n",
       "      <td>睢冉</td>\n",
       "      <td>男</td>\n",
       "      <td>1992年</td>\n",
       "      <td>26</td>\n",
       "      <td>192</td>\n",
       "      <td>95</td>\n",
       "      <td>篮球</td>\n",
       "      <td>山西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>124</td>\n",
       "      <td>王哲林</td>\n",
       "      <td>男</td>\n",
       "      <td>1994年</td>\n",
       "      <td>24</td>\n",
       "      <td>214</td>\n",
       "      <td>110</td>\n",
       "      <td>篮球</td>\n",
       "      <td>福建省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>155</td>\n",
       "      <td>易建联</td>\n",
       "      <td>男</td>\n",
       "      <td>1987年</td>\n",
       "      <td>31</td>\n",
       "      <td>213</td>\n",
       "      <td>113</td>\n",
       "      <td>篮球</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>161</td>\n",
       "      <td>周鹏</td>\n",
       "      <td>男</td>\n",
       "      <td>1989年</td>\n",
       "      <td>29</td>\n",
       "      <td>206</td>\n",
       "      <td>90</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>175</td>\n",
       "      <td>周琦</td>\n",
       "      <td>男</td>\n",
       "      <td>1996年</td>\n",
       "      <td>22</td>\n",
       "      <td>217</td>\n",
       "      <td>95</td>\n",
       "      <td>篮球</td>\n",
       "      <td>河南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>176</td>\n",
       "      <td>翟晓川</td>\n",
       "      <td>男</td>\n",
       "      <td>1993年</td>\n",
       "      <td>25</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>篮球</td>\n",
       "      <td>河北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>177</td>\n",
       "      <td>赵继伟</td>\n",
       "      <td>男</td>\n",
       "      <td>1995年</td>\n",
       "      <td>23</td>\n",
       "      <td>185</td>\n",
       "      <td>77</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>178</td>\n",
       "      <td>邹雨宸</td>\n",
       "      <td>男</td>\n",
       "      <td>1996年</td>\n",
       "      <td>22</td>\n",
       "      <td>208</td>\n",
       "      <td>108</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        姓名 性别 出生年份（年）  年龄（岁）  身高(cm)  体重(kg)  项目        省份\n",
       "16   丁彦雨航   男   1993年     25     200      91  篮球  新疆维吾尔自治区\n",
       "28    郭艾伦   男   1993年     25     192      85  篮球       辽宁省\n",
       "48    李慕豪   男   1992年     26     225     111  篮球      贵州贵阳\n",
       "106    睢冉   男   1992年     26     192      95  篮球       山西省\n",
       "124   王哲林   男   1994年     24     214     110  篮球      福建省 \n",
       "155   易建联   男   1987年     31     213     113  篮球       广东省\n",
       "161    周鹏   男   1989年     29     206      90  篮球       辽宁省\n",
       "175    周琦   男   1996年     22     217      95  篮球      河南省 \n",
       "176   翟晓川   男   1993年     25     204     100  篮球      河北省 \n",
       "177   赵继伟   男   1995年     23     185      77  篮球       辽宁省\n",
       "178   邹雨宸   男   1996年     22     208     108  篮球       辽宁省"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ydy_a = ydy.loc[ydy['性别']=='男']\n",
    "llydy = ydy_a.loc[ydy['项目']=='篮球']\n",
    "llydy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "年龄（岁）      9\n",
       "身高(cm)    40\n",
       "体重(kg)    36\n",
       "dtype: int64"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "llydy[['年龄（岁）','身高(cm)','体重(kg)']].agg(lambda x:x.max()-x.min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "ydy['BMI'] = ydy['体重(kg)']/(ydy['身高(cm)']/100)**2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "      <th>出生年份（年）</th>\n",
       "      <th>年龄（岁）</th>\n",
       "      <th>身高(cm)</th>\n",
       "      <th>体重(kg)</th>\n",
       "      <th>项目</th>\n",
       "      <th>省份</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>陈楠</td>\n",
       "      <td>女</td>\n",
       "      <td>1983年</td>\n",
       "      <td>35</td>\n",
       "      <td>197</td>\n",
       "      <td>90</td>\n",
       "      <td>篮球</td>\n",
       "      <td>山东省</td>\n",
       "      <td>23.190497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>白发全</td>\n",
       "      <td>男</td>\n",
       "      <td>1986年</td>\n",
       "      <td>32</td>\n",
       "      <td>175</td>\n",
       "      <td>64</td>\n",
       "      <td>铁人三项</td>\n",
       "      <td>云南省</td>\n",
       "      <td>20.897959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>陈晓佳</td>\n",
       "      <td>女</td>\n",
       "      <td>1988年</td>\n",
       "      <td>30</td>\n",
       "      <td>180</td>\n",
       "      <td>70</td>\n",
       "      <td>篮球</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>21.604938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>陈倩</td>\n",
       "      <td>女</td>\n",
       "      <td>1987年</td>\n",
       "      <td>31</td>\n",
       "      <td>163</td>\n",
       "      <td>54</td>\n",
       "      <td>女子现代五项</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>20.324438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>曹忠荣</td>\n",
       "      <td>男</td>\n",
       "      <td>1981年</td>\n",
       "      <td>37</td>\n",
       "      <td>180</td>\n",
       "      <td>73</td>\n",
       "      <td>男子现代五项</td>\n",
       "      <td>上海市</td>\n",
       "      <td>22.530864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>174</td>\n",
       "      <td>赵芸蕾</td>\n",
       "      <td>女</td>\n",
       "      <td>1986年</td>\n",
       "      <td>32</td>\n",
       "      <td>173</td>\n",
       "      <td>62</td>\n",
       "      <td>羽毛球</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>20.715694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>175</td>\n",
       "      <td>周琦</td>\n",
       "      <td>男</td>\n",
       "      <td>1996年</td>\n",
       "      <td>22</td>\n",
       "      <td>217</td>\n",
       "      <td>95</td>\n",
       "      <td>篮球</td>\n",
       "      <td>河南省</td>\n",
       "      <td>20.174563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>176</td>\n",
       "      <td>翟晓川</td>\n",
       "      <td>男</td>\n",
       "      <td>1993年</td>\n",
       "      <td>25</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>篮球</td>\n",
       "      <td>河北省</td>\n",
       "      <td>24.029220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>177</td>\n",
       "      <td>赵继伟</td>\n",
       "      <td>男</td>\n",
       "      <td>1995年</td>\n",
       "      <td>23</td>\n",
       "      <td>185</td>\n",
       "      <td>77</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "      <td>22.498174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>178</td>\n",
       "      <td>邹雨宸</td>\n",
       "      <td>男</td>\n",
       "      <td>1996年</td>\n",
       "      <td>22</td>\n",
       "      <td>208</td>\n",
       "      <td>108</td>\n",
       "      <td>篮球</td>\n",
       "      <td>辽宁省</td>\n",
       "      <td>24.963018</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>179 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       姓名 性别 出生年份（年）  年龄（岁）  身高(cm)  体重(kg)      项目     省份        BMI\n",
       "0     陈楠   女   1983年     35     197      90      篮球    山东省  23.190497\n",
       "1    白发全   男   1986年     32     175      64    铁人三项    云南省  20.897959\n",
       "2    陈晓佳   女   1988年     30     180      70      篮球    江苏省  21.604938\n",
       "3     陈倩   女   1987年     31     163      54  女子现代五项  江苏省    20.324438\n",
       "4    曹忠荣   男   1981年     37     180      73  男子现代五项    上海市  22.530864\n",
       "..    ... ..     ...    ...     ...     ...     ...    ...        ...\n",
       "174  赵芸蕾   女   1986年     32     173      62     羽毛球    湖北省  20.715694\n",
       "175   周琦   男   1996年     22     217      95      篮球   河南省   20.174563\n",
       "176  翟晓川   男   1993年     25     204     100      篮球   河北省   24.029220\n",
       "177  赵继伟   男   1995年     23     185      77      篮球    辽宁省  22.498174\n",
       "178  邹雨宸   男   1996年     22     208     108      篮球    辽宁省  24.963018\n",
       "\n",
       "[179 rows x 9 columns]"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ydy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
